1.innodb 中的锁有哪几种?

# 这个查询用于查看当前正在运行的InnoDB事务的信息，包括事务ID、事务状态、锁定等信息。
SELECT * FROM information_schema.INNODB_TRX;

# 这个查询用于查看正在等待锁定的InnoDB事务的信息，包括等待锁的事务ID、等待锁的资源和锁等待时间等。
SELECT * FROM `sys`.`innodb_lock_waits`;

#这个查询用于查看当前正在持有的数据锁定信息，包括锁定的对象、锁定类型和锁定持有者等。
SELECT * FROM performance_schema.data_locks;

#这个查询用于查看正在等待数据锁定的事务信息，包括等待锁定的事务ID、等待锁的资源和锁等待时间等。
SELECT * FROM performance_schema.data_lock_waits;

InnoDB 存储引擎实现了如下两种标准的行级锁：

• 共享锁（SLock），允许事务读一行数据。
• 排他锁（XLock），允许事务删除或更新一行数据。

如果一个事务 T1 已经获得了行 r 的共享锁，那么另外的事务 T2 可以立即获得行 r 的共享锁，因为读取并没有改变行 r 的数据，称这种情况为锁兼容（Lock Compatible）。但若有其他的事务 T3 想获得行 r 的排他锁，则其必须等待事务 T1、T2 释放行 r 上的共享锁——这种情况称为锁不兼容。

2.innodb 意向锁?

InnoDB 存储引擎支持多粒度（granularity）锁定，这种锁定允许事务在行级上的锁和表级上的锁同时存在。为了支持在不同粒度上进行加锁操作，InnoDB 存储引擎支持一种额外的锁方式，称为意向锁（Intention Locks）。意向锁将锁定的对象分为多个层次，意味着事务希望在更加细粒度（fine granularity）上加行锁。意向锁是一种不与行级锁冲突的表级锁。

如果需要用到表锁的话，如何判断表中的记录没有行锁呢？一行一行遍历肯定是不行，性能太差。我们需要用到一个叫做意向锁的东西来快速判断是否可以对某个表使用表锁。

意向锁是表级锁，共有两种：

• 意向共享锁（Intention Shared Lock，IS 锁）：事务有意向对表中的某些记录加共享锁（S 锁），加共享锁前必须先取得该表的 IS 锁。
• 意向排他锁（Intention Exclusive Lock，IX 锁）：事务有意向对表中的某些记录加排他锁（X 锁），加排他锁之前必须先取得该表的 IX 锁。

由于 InnoDB 存储引擎支持的是行级别的锁，因此意向锁其实不会阻塞除全表扫以外的任何请求。

3.innodb 行锁的三种算法?

InnoDB 存储引擎有 3 种行锁的算法，其分别是：

• Record Lock 记录锁：单个行记录上的锁
• Gap Lock 间隙锁：锁定一个范围，但不包含记录本身
• Next-Key Lock 临键锁 ∶Gap Lock+Record Lock，锁定一个范围，并且锁定记录本身。

Record Lock 总是会去锁住索引记录，如果 InnoDB 存储引擎表在建立的时候没有设置任何一个索引，那么这时 InnoDB 存储引擎会使用隐式的主键来进行锁定。

Gap Lock:锁定是一个范围，但是不包含边界，开开区间.

Next-Key Lock 是结合了 Gap Lock 和 Record Lock 的一种锁定算法，在 Next-Key Lock 算法下，InnoDB 对于行的查询都是采用这种锁定算法。

在 InnoDB 默认的事务隔离级别下，即 REPEATABLE READ 下，InnoDB 存储引擎采用 Next-Key Locking 这种锁定算法。例如一个索引有 10，11，13 和 20 这四个值，那么该索引可能被 Next-Key Locking 的区间为：

(-∞，10] (10，11] (11，13] (13，20] (20，+∞)

DROP TABLE IF EXISTS t;
CREATE TABLE t( a INT PRIMARY KEY);
INSERT INTO t SELECT 1;
INSERT INTO t SELECT 2;
INSERT INTO t SELECT 5;

当查询的索引含有唯一属性时，InnoDB 存储引擎会对 Next-Key Lock 进行优化，将其降级为 Record Lock，即仅锁住索引本身，而不是范围。

什么是唯一属性，其实就是我们所说的能够标识该行数据唯一的标识。unique 字段。比如：主键就是唯一的，不重复的。我们也可以自己设计多个字段组合不重复，唯一的。

表 t 共有 1、2、5 三个值。在上面的例子中，在会话 A 中首先对 a=5 进行 X 锁定。而由于 a 是主键且唯一，因此锁定的仅是 5 这个值，而不是(2，5)这个范围，这样在会话 B 中插入值 4 而不会阻塞，可以立即插入并返回。即锁定由 Next-Key Lock 算法降级为了 Record Lock，从而提高应用的并发性。

CREATE TABLE z(a INT，b INT，PRIMARY KEY(a)，KEY(b));
INSERT INTO z SELECT 1，1;
INSERT INTO z SELECT 3，1;
INSERT INTO z SELECT 5， 3;
INSERT INTO z SELECT 7，6;
INSERT INTO z SELECT 10，8;

#sql1
SELECT * FROM z WHERE a=5 LOCK IN SHARE MODE;

#sql2
INSERT INTO z SELECT 4，2;

#sql3
INSERT INTO z SELECT 6，5;

表 z 的列 b 是辅助索引，若在会话 A 中执行下面的 SQL 语句：

SELECT * FROM z WHERE b=3 FOR UPDATE;

很明显，这时 SQL 语句通过索引列 b 进行查询，该列不是唯一属性，因此其使用传统的 Next-Key Locking 技术加锁，并且由于有两个索引，其需要分别进行锁定。对于聚簇索引（primay-key a），其仅对列 a 等于 5 的索引加上 Record Lock。而对于辅助索引 b，其加上的是 Next-Key Lock，锁定的范围是(1，3)。特别需要注意的是，InnoDB 存储引擎还会对辅助索引下一个键值加上 gap lock，即还有一个辅助索引范围为(3，6)的锁。

第一个 SQL 语句不能执行，因为在会话 A 中执行的 SQL 语句已经对聚集索引中列 a=5 的值加上 X 锁，因此执行会被阻塞。

第二个 SQL 语句，主键插入 4，没有问题，但是插入的辅助索引值 2 在锁定的范围(1，3)中，因此执行同样会被阻塞。

第三个 SQL 语句，插入的主键 6 没有被锁定，5 也不在范围(1，3)之间。但插入的值 5 在另一个锁定的范围(3，6)中，故同样需要等待。

4.next-key lock 作用?

在默认的事务隔离级别下，即 REPEATABLE READ 下，InnoDB 存储引擎采用 Next-Key Locking 机制来避免 Phantom Problem（幻读问题，也称不可重复读）。Phantom Problem 是指在同一事务下，连续执行两次同样的 SQL 语句可能导致不同的结果。（在 READ COMMITTED 事务隔离级别下会出现）

CREATE TABLE z(a INT，b INT，PRIMARY KEY(a)，KEY(b));
INSERT INTO z SELECT 1，1;
INSERT INTO z SELECT 3，1;
INSERT INTO z SELECT 5， 3;
INSERT INTO z SELECT 7，6;
INSERT INTO z SELECT 10，8;

在同一事务中，若此时执行语句：

SELECT * FROM z WHERE b=3 FOR UPDATE;

执行两次，中间间隔 10 秒时间执行。可以肯定的说，我们会得到第三行数据的结果，即（5，3）。此时我们知道，会有一个 Record Lock 锁定主键 5，还会有一个 gap lock 锁定（1，3）和（3，6）。

假设：我们分析下，若此时没有 gap lock（1，3）和（3，6），如果只有 Record Lock 锁定主键 5 会不会造成幻读。

分析：我们在第一次 select 完成之后，第二次 select 之前，插入一条数据:

INSERT INTO z SELECT 20，3;

这条数据是可以插入成功的，因为我们只有一个 record lock 锁定了主键 5，对于新插入的数据主键为 20，可以插入，且无重复。插入完成后，第二次 select 得到了两个值，（5，3）（20，3）。这就造成了同一事物中，第一次读取和第二次读取的结果不一样，出现幻读。如果是 gap lock，不能锁定记录本身 3，如果有 next-key lock，插入就会被阻塞，不会出现幻读。

Next-Key Locking解决幻读问题整理：

1. 幻读是连续读取同一个 sql，出现不同的结果；
2. a 是主键，b 是辅助索引；
3. 则查询 b=3 时，如果不采用临键锁，会读取到多条数据；
4. 记录锁针对的是主键唯一的锁定，gap lock 间隙锁是范围锁，左开右开区间，无法阻止 b 等于其他值的插入；
5. 如果是临键锁，既可以防止主键的重复，也可以用左闭右闭范围限定的 gap lock 间隙锁限定 b 的值；

5.自增长锁?

自增长计数器（auto-increment counter):插入操作会依据这个自增长的计数器值加 1 赋予自增长列。这个方式称做 AUTO-INC Locking。这种锁其实是采用一种特殊的机制，为了提高插入的性能，锁不是在一个事务完成后才释放，而是在完成对自增长值插入的 SQL 语句后立即释放。

插入类型：

插入类型	说明
insert-like	所有的插入语句，如 INSERT、REPLACE、INSERT…SELECT、REPLACE…SEECT、LOAD DATA 等
simple inserts	能在插入前就确定插入行数的语句，包括 INSERT、REPLACE 等，不包含 INSERT…ON DUPLICATE KEY UPDATE 这类 SQL 语句
bulk inserts	在插入前不能确定得到插入行数的语句，如 INSERT…SELECT、REPLACE…SELECT、LOAD DATA 等
mixed-mode inserts	插入中有一部分的值是自增长的，有一部分是确定的，如 INSERT INTO t1(c1,c2)VALUES(1,'a'),(NULL,'b'),(5,'c'),(NULL,'d')，也可以是指 INSERT…ON DUPLICATE KEY UPDATE 这类 SQL 语句

虽然 AUTO-INC Locking 从一定程度上提高了并发插入的效率，但还是存在一些性能上的问题。首先，对于有自增长值的列的并发插入性能较差，事务必须等待前一个插入的完成（虽然不用等待事务的完成）。其次，对于 INSERT…SELECT 的大数据量的插入会影响插入的性能，因为另一个事务中的插入会被阻塞。

从 MySQL5.1.22 版本开始，InnoDB 存储引擎中提供了一种轻量级互斥量的自增长实现机制，这种机制大大提高了自增长值插入的性能。并且从该版本开始，InnoDB 存储引擎提供了一个参数 innodb_autoinc_lock_mode 来控制自增长的模式，该参数的默认值为 1。

参数innodb_autoinc_lock_mode 的说明：

参数名	说明
innodb_autoinc_lock_mode
0	这是 MySQL5.1.22 版本之前的自增长实现方式，通过表锁的 AUTO-INC Locking 方式，因为有了新的自增长实现方式，0 这个选项不应该是新版用户的首选项
1	这是该参数的默认值。对于该值会用互斥量(mutex)去对内“simple inserts,存中的计数器进行累加的操作。对于“bulk inserts”，还是使用传统表锁的 AUTO-INC Locking 方式。在这种配置下，如果不考虑回滚操作，对于自增值列的增长还是连续的。并且在这种方式下，statement-based 方式的 replication 还是能很好地工作。需要注意的是，如果已经使用 AUTO-INCLocing 方式去产生自增长的值，而这时需要再进行“simple inserts”的操作时，还是需要等待 AUTO-INC Locking 的释放
2	在这个模式下，对于所有“INSERT-like”自增长值的产生都是通过互斥量，而不是 AUTO-INC Locking 的方式。显然，这是性能最高的方式。然而，这会带来一定的问题。因为并发插入的存在，在每次插入时，自增长的值可能不是连续的。此外，最重要的是，基于 Statement-Base Replication 会出现问题。因此，使用这个模式，任何时候都应该使用 row-base replication。这样才能保证最大的并发性能及 replication 主从数据的一致

6.lock 和 latch 的区别?

latch 一般称为闩锁(轻量级的锁)，因为其要求锁定的时间必须非常短。若持续的时间长，则应用的性能会非常差。在 InnoDB 存储引擎中，latch 又可以分为 mutex(互斥量)和 rwlock(读写锁)。其目的是用来保证并发线程操作临界资源的正确性，并且通常没有死锁检测的机制。 lock 的对象是事务，用来锁定的是数据库中的对象，如表、页、行。并且一般 lock 的对象仅在事务 commit 或 rollback 后进行释放(不同事务隔离级别释放的时间可能不同)。此外，lock，正如在大多数数据库中一样，是有死锁机制的。

7.一致性锁定读?

SELECT 语句支持两种一致性的锁定读(locking read)操作:

• SELECT...FOR UPDATE
• SELECT..LOCKIN SHARE MODE

SELECT...FOR UPDATE 对读取的行记录加一个 X 锁，其他事务不能对已锁定的行加上任何锁。

SELECT..LOCK IN SHARE MODE 对读取的行记录加一个 S 锁，其他事务可以向被锁定的行加 S 锁，但是如果加 X 锁，则会被阻塞。

当事务提交了，锁锁也就释放了。因此在使用上述两句 SELECT 锁定语句时，务必加上 BEGIN，START TRANSACTION 或者 SET AUTOCOMMIT=0。

8.什么是一致性非锁定读的?

MVCC 的核心:

• 表的隐藏列：DB_TRX_ID（记录操作当前数据的事务 ID）、DB_ROLL_PTR（记录上个版本数据的地址，指向 undo log）。
• undo log：记录数据各版本的修改历史，即“版本链”。
• Read View：读视图，用于判断哪些数据版本对当前 SELECT 可见。

RC 级别下，事务中每次 SELECT请求都会重新创建read view；

RR 级别下，事务中的第一个 SELECT请求才开始创建read view；

• DB_TRX_ID：记录插入或者更新该行数据的最后一个事务 ID。
• DB_ROW_ID：隐藏的自增 ID，当数据库表没有指定主键的时候，会自动生成。
• DB_ROLL_PTR：回滚指针，7 字节，指向写入回滚段的 undo log 记录。指向该行的上一个记录。

一致性的非锁定读（consistent nonlocking read）是指 InnoDB 存储引擎通过行多版本控制（multi versioning）的方式来读取当前执行时间数据库中行的数据。如果读取的行正在执行 DELETE 或 UPDATE 操作，这时读取操作不会因此去等待行上锁的释放。相反地，InnoDB 存储引擎会去读取行的一个快照数据。

之所以称其为非锁定读，因为不需要等待访问的行上 X 锁的释放。快照数据是指该行的之前版本的数据，该实现是通过 undo log 来完成。而undo log 用来在事务中回滚数据，因此快照数据本身是没有额外的开销。此外，读取快照数据是不需要上锁的，因为没有事务需要对历史的数据进行修改操作。

快照数据其实就是当前行数据之前的历史版本，每行记录可能有多个版本。如右图显示的，一个行记录可能有不止一个快照数据，一般称这种技术为行多版本技术。由此带来的并发控制，称之为多版本并发控制（Multi Version Concurrency Control，MVCC）。

9.MVCC 实现原理

快照读和当前读:

• 快照读:不加锁的非阻塞读，select
• 当前读:
  • select...lock in share mode;
  • select...for update
  • update、insert、delete

在事务隔离级别 READ COMMITTED 和 REPEATABLE READ(InnoDB 存储引擎的默认事务隔离级别)下，InnoDB 存储引擎使用非锁定的一致性读。然而，对于快照数据的定义却不相同。在 READ COMMITTED 事务隔离级别下，对于快照数据，非一致性读总是读取被锁定行的最新一份快照数据。而在 REPEATABLE READ 事务隔离级别下，对于快照数据，非一致性读总是读取事务开始时的行数据版本。

举个例子，a 开启事务，读取 id=1 的数据，未提交事务，b 开启事务，set id=3，b 事务未提交时，在 READ COMMITTED 和 REPEATABLE READ 隔离级别下，读到的都是 id=1，因为只有一个快照，当 b 提交后，再读 id=1 时，READ COMMITTED 会读到空数据，因为读的是最新的行快照，REPEATABLE READ 读到的还是 id=1 的数据，会一直用事务开始读到的快照.

对于 READ COMMITTED 的事务隔离级别而言，从数据库理论的角度来看，其违反了事务 ACID 中的 I 的特性，即隔离性。

MVCC 具体实现过程?

Read View:读视图，某一时刻的一个 trx_id 事务快照

class ReadView {
  // 省略...
 private:
  /** 高水位，大于等于这个ID的事务均不可见*/
  trx_id_t  m_low_limit_id;

  /** 低水位：小于这个ID的事务均可见 */
  trx_id_t m_up_limit_id;

  /** 创建该 Read View 的事务ID*/
  trx_id_t m_creator_trx_id;

  /** 创建视图时的活跃事务id列表*/
  ids_t m_ids;

  /** 配合purge，标识该视图不需要小于m_low_limit_no的UNDO LOG，
   * 如果其他视图也不需要，则可以删除小于m_low_limit_no的UNDO LOG*/
  trx_id_t m_low_limit_no;

  /** 标记视图是否被关闭*/
  bool m_closed;

  // 省略...
};

核心属性:

• m_ids：创建 ReadView 时当前系统中活跃的事务 Id 列表，可以理解为生成 ReadView 那一刻还未执行提交的事务，并且该列表是个升序列表。

• m_up_limit_id：低水位，取 m_ids 列表的第一个节点，因为 m_ids 是升序列表，因此也就是 m_ids 中事务 Id 最小的那个。

• m_low_limit_id：高水位，生成 ReadView 时系统将要分配给下一个事务的 Id 值。

• m_creator_trx_id：创建该 ReadView 的事务的事务 Id。

可见性： 说白了，这个读视图的作用就是为了让我们能够得到当前事务该读到什么版本的数据，它遵循以下的算法：

• 如果被访问版本的 trx_id 与 ReadView 中的 m_creator_trx_id 值相同，意味着当前事务在访问它自己修改过的记录，所以该版本可以被当前事务访问。
• 如果被访问版本的 trx_id 小于 ReadView 中的 m_up_limit_id（低水位），表明被访问版本的事务在当前事务生成 ReadView 前已经提交，所以该版本可以被当前事务访问。
• 如果被访问版本的 trx_id 大于等于 ReadView 中的 m_low_limit_id（高水位），表明被访问版本的事务在当前事务生成 ReadView 后才开启，所以该版本不可以被当前事务访问。
• 如果被访问版本的 trx_id 属性值在 ReadView 的 m_up_limit_id 和 m_low_limit_id 之间，那就需要判断 trx_id 属性值是不是在 m_ids 列表中，这边会通过二分法查找。如果在，说明创建 ReadView 时生成该版本的事务还是活跃的，该版本不可以被访问；如果不在，说明创建 ReadView 时生成该版本的事务已经被提交，该版本可以被访问。

10.什么是丢失更新?

丢失更新是锁导致的问题，简单来说就是一个事务的更新操作会被另一个事务的更新操作所覆盖，从而导致数据的不一致。例如：

1. 事务 T1 将行记录 r 更新为 v1，但是事务 T1 并未提交。
2. 与此同时，事务 T2 将行记录 r 更新为 v2，事务 T2 未提交。
3. 事务 T1 提交。
4. 事务 T2 提交。

但是，在当前数据库的任何隔离级别下，都不会导致数据库理论意义上的丢失更新问题。这是因为，即使是 READ UNCOMMITTED 的事务隔离级别，对于行的 DML 操作(增删改查)，需要对行或其他粗粒度级别的对象加锁。因此在上述步骤 2 中，事务 T2 并不能对行记录 r 进行更新操作，其会被阻塞，直到事务 T1 提交。

虽然数据库能阻止丢失更新问题的产生，但是在生产应用中还有另一个逻辑意义的丢失更新问题，而导致该问题的并不是因为数据库本身的问题。实际上，在所有多用户计算机系统环境下都有可能产生这个问题。简单地说来，出现下面的情况时，就会发生丢失更新：

1. 事务 T1 查询一行数据，放入本地内存，并显示给一个终端用户 User1。

2. 事务 T2 也查询该行数据，并将取得的数据显示给终端用户 User2。

3. User1 修改这行记录，更新数据库并提交。

4. User2 修改这行记录，更新数据库并提交。

显然，这个过程中用户 User1 的修改更新操作“丢失”了，而这可能会导致一个“恐怖”的结果。 要避免丢失更新发生，需要让事务在这种情况下的操作变成串行化，而不是并行的操作。即在上述四个步骤的 1 中，对用户读取的记录加上一个排他 X 锁。同样，在步骤 2 的操作过程中，用户同样也需要加一个排他 X 锁。通过这种方式，步骤 2 就必须等待一步骤 1 和步骤 3 完成，最后完成步骤 4。

11.什么是脏读?

脏读指的就是在不同的事务下，当前事务可以读到另外事务未提交的数据，简单来说就是可以读到脏数据。

Time	回话 A	回话 B
1	set @@tx_isolation='read-uncommitted';
2		set @@tx_isolation='read-uncommitted';
3		begin;
4		select * from t //得到一行
5	insert into t select 2;
6		select * from t //得到二行

事务的隔离级别进行了更换，由默认的 REPEATABLE READ 换成了 READ UNCOMMITTED。因此在会话 A 中，在事务并没有提交的前提下，会话 B 中的两次 SELECT 操作取得了不同的结果，并且 2 这条记录是在会话 A 中并未提交的数据，即产生了脏读，违反了事务的隔离性。脏读现象在生产环境中并不常发生，从上面的例子中就可以发现，脏读发生的条件是需要事务的隔离级别为 READ UNCOMMITTED，而目前绝大部分的数据库都至少设置成 READ COMMITTED。

脏读（Dirty read）: 当一个事务正在访问数据并且对数据进行了修改，而这种修改还没有提交到数据库中，这时另外一个事务也访问了这个数据，然后使用了这个数据。因为这个数据是还没有提交的数据，那么另外一个事务读到的这个数据是“脏数据”，依据“脏数据”所做的操作可能是不正确的。

12.什么是不可重复读?

不可重复读（Unrepeatable read）:

指在一个事务内多次读同一数据。在这个事务还没有结束时，另一个事务也访问该数据。那么，在第一个事务中的两次读数据之间，由于第二个事务的修改导致第一个事务两次读取的数据可能不太一样。这就发生了在一个事务内两次读到的数据是不一样的情况，因此称为不可重复读。

13.什么是幻读?

幻读（Phantom read）:幻读与不可重复读类似。它发生在一个事务（T1）读取了几行数据，接着另一个并发事务（T2）插入了一些数据时。在随后的查询中，第一个事务（T1）就会发现多了一些原本不存在的记录，就好像发生了幻觉一样，所以称为幻读。

14.如何解决幻读问题?

快照读和当前读:

• 快照读:不加锁的非阻塞读，select
• 当前读:
  • select...lock in share mode;
  • select...for update
  • update、insert、delete

InnoDB 存储引擎默认的事务隔离级别是 REPEATABLE READ，该隔离级别下，其采用 Next-Key Locking 的方式来进行加锁，在隔离级别为 READ COMMITTED 下，其仅采用 Record lock 进行加锁.

• 快照读 ：由 MVCC 机制来保证不出现幻读。

• 当前读 ： 使用 Next-Key Lock 进行加锁来保证不出现幻读，Next-Key Lock 是行锁（Record Lock）和间隙锁（Gap Lock）的结合，行锁只能锁住已经存在的行，为了避免插入新行，需要依赖间隙锁。

Next-Key Locking解决幻读问题整理：

1. 幻读是连续读取同一个 sql，出现不同的结果；
2. a 是主键，b 是辅助索引；
3. 则查询 b=3 时，如果不采用临键锁，会读取到多条数据；
4. 记录锁针对的是主键唯一的锁定，gap lock 间隙锁是范围锁，左开右开区间，无法阻止 b 等于其他值的插入；
5. 如果是临键锁，既可以防止主键的重复，也可以用左闭右闭范围限定的 gap lock 间隙锁限定 b 的值；

15.什么是插入意向锁?

插入意向锁(Insert Intention Locks):我们说一个事务在插入一条记录时需要判断一下插入位置是不是被别的事务加了 gap 锁( next-key 锁也包含 gap 锁)，如果有的话，插入操作需要等待，直到拥有 gap 锁的那个事务提交。但是”InnoDB 规定事务在等待的时候也需要在内存中生成一个锁结构，表明有事务想在某个间隙中插入新记录，但是现在在等待。InnoDB 就把这种类型的锁命名为 Insert Intention Locks ，官方的类型名称为: LOCK_INSERT_INTENTION，我们称为插入意向锁。插入意向锁是一种 Gap 锁，不是意向锁，在 insert 操作时产生。

插入意向锁是在插入一条记录行前，由 INSERT 操作产生的一种间隙锁。该锁用以表示插入意向，当多个事务在 同一区间(gap) 插入位置不同的多条数据时，事务之间不需要互相等待。假设存在两条值分别为 4 和 7 的记 录，两个不同的事务分别试图插入值为 5 和 6 的两条记录，每个事务在获取插入行上独占的(排他)锁前，都会 获取(4， 7)之间的间隙锁，但是因为数据行之间并不冲突，所以两个事务之间并不会产生冲突(阻塞等待)。 总结来说，插入意向锁的特性可以分成两部分:

• 插入意向锁是一种特殊的间隙锁，间隙锁可以锁定开区间内的部分记录。
• 插入意向锁之间互不排斥，所以即使多个事务在同一区间插入多条记录，只要记录本身(主键、唯一索引)不冲突，那么事务之间就不会出现冲突等待。

注意，虽然插入意向锁中含有意向锁三个字，但是它并不属于意向锁而属于间隙锁，因为意向锁是表锁而插入意 向锁是行锁。

插入意向锁的生成时机:每插入一条新记录，都需要看一下待插入记录的下一条记录上是否已经被加了间隙锁，如果已加间隙锁，那 Insert 语句会被阻塞，并生成一个插入意向锁 。

16.临键锁退化机制?

1.唯一索引的等值查询

• 当查询的记录是存在的，在用「唯一索引进行等值查询」时，next-key lock 会退化成「记录锁」。

• 当查询的记录是不存在的，在用「唯一索引进行等值查询」时，next-key lock 会退化成「间隙锁」。

2.唯一索引的范围查询

• next-key lock 不会退化

3.非唯一索引等值查询

• 当查询的记录存在时，除了会加 next-key lock 外，还额外加间隙锁，也就是会加两把锁。
• 当查询的记录不存在时，只会加 next-key lock，然后会退化为间隙锁，也就是只会加一把锁。

4.非唯一索引范围查询

• 普通索引范围查询，next-key lock 不会退化为间隙锁和记录锁。

17.如何预防数据库死锁?

死锁是指两个或两个以上的事务在执行过程中，因争夺锁资源而造成的一种互相等待的现象。若无外力作用，事务都将无法推进下去。解决死锁问题最简单的方式是不要有等待，将任何的等待都转化为回滚，并且事务重新开始。毫无疑问，这的确可以避免死锁问题的产生。然而在线上环境中，这可能导致并发性能的下降，甚至任何一个事务都不能进行。而这所带来的问题远比死锁问题更为严重，因为这很难被发现并且浪费资源。

解决死锁问题最简单的一种方法是超时，即当两个事务互相等待时，当一个等待时间超过设置的某一阈值时，其中一个事务进行回滚，另一个等待的事务就能继续进行。在 InnoDB 存储引擎中，参数 innodb_lock_wait_timeout 用来设置超时的时间。

超时机制虽然简单，但是其仅通过超时后对事务进行回滚的方式来处理，或者说其是根据 FIFO 的顺序选择回滚对象。但若超时的事务所占权重比较大，如事务操作更新了很多行，占用了较多的 undo log，这时采用 FIFO 的方式，就显得不合适了，因为回滚这个事务的时间相对另一个事务所占用的时间可能会很多。

因此，除了超时机制，当前数据库还都普遍采用 wait-for graph（等待图）的方式来进行死锁检测。较之超时的解决方案，这是一种更为主动的死锁检测方式。InnoDB 存储引擎也采用的这种方式。wait-for graph 要求数据库保存以下两种信息:

• 锁的信息链表

• 事务等待链表

在等待图中，事务 T1 指向 T2 边的定义为：

• 事务 T1 等待事务 T2 所占用的资源
• 事务 T1 最终等待 T2 所占用的资源，也就是事务之间在等待相同的资源，而事务 T1 发生在事务 T2 的后面

通过等待图可以发现存在回路（t1，t2），因此存在死锁。通过上述的介绍，可以发现 wait-for graph 是一种较为主动的死锁检测机制，在每个事务请求锁并发生等待时都会判断是否存在回路，若存在则有死锁，通常来说 InnoDB 存储引擎选择回滚 undo 量最小的事务。

18.Mysql 发生死锁情况

在 MySQL 数据库中，死锁是指两个或多个事务相互等待对方持有的资源，导致它们无法继续执行，并且没有办法通过自动机制解决这种互相等待的情况。这种情况下，MySQL 将选择其中一个事务进行回滚，以解除死锁。

死锁发生的常见情况包括：

1. 同时更新多个资源：如果多个事务同时尝试更新相同的数据行，而且更新操作涉及到对数据行的锁定，可能导致死锁。例如，事务 A 锁定了行 X，同时事务 B 锁定了行 Y，然后它们尝试更新对方所持有的资源，就可能出现死锁情况。
2. 交叉依赖：如果事务 A 持有资源 X 并等待资源 Y，而事务 B 持有资源 Y 并等待资源 X，就会发生死锁。这种情况下，两个事务互相依赖对方持有的资源，导致死锁。
3. 并发控制不当：如果数据库中的并发控制机制（如锁、事务隔离级别等）配置不当或实现有误，可能导致死锁的发生。
4. 索引缺失：如果数据库表没有适当的索引，可能导致某些操作涉及大量行的扫描，增加了死锁的风险。
5. 长时间的事务：如果某个事务持有锁的时间过长，而其他事务需要等待该锁，就可能出现死锁。

为了避免死锁，可以采取以下措施：

1. 合理设计数据库表的索引，以减少锁冲突和提高查询性能。
2. 尽量缩短事务的执行时间，减少事务持有锁的时间。
3. 选择合适的事务隔离级别，根据应用的需求进行调整。
4. 使用 MySQL 的死锁检测和解决机制，例如设置innodb_deadlock_detect参数为 ON，MySQL 将自动检测并回滚死锁事务。
5. 在代码中使用合理的事务管理，确保事务提交或回滚的顺序不会导致死锁。

19.mysql 出现死锁

锁等待超时 Lock wait timeout exceeded; try restarting transaction，是当前事务在等待其它事务释放锁资源造成的

查看当前数据库的线程情况：

show full PROCESSLIST;

查看事务表:

再到 INNODB_TRX 事务表中查看，看 trx_mysql_thread_id 是否在 show full processlist 里面的 sleep 线程中（ INNODB_TRX 表的 trx_mysql_thread_id 字段对应 show full processlist 中的 Id）；如果在，就说明这个 sleep 的线程事务一直没有 commit 或者 rollback，而是卡住了，需要我们手动删除。

select * from information_schema.innodb_trx;

trx_mysql_thread_id:将找到的 trx_mysql_thread_id 手动删除。

20.全局锁和表级锁?

给 mysql 添加全局锁

Flush tables with read lock;

释放

unlock tables;

表级别的共享锁，也就是读锁；

lock tables t_student read;

表级别的独占锁，也就是写锁；

lock tables t_stuent wirte;

unlock tables;

21.查看加锁信息?

select * from performance_schema.data_locks\G;

通过执行上述 sql，共加了两个锁，分别是：

• 表锁：X 类型的意向锁；
• 行锁：X 类型的间隙锁；

这里我们重点关注行锁，LOCK_TYPE 中的 RECORD 表示行级锁，而不是记录锁的意思，通过 LOCK_MODE 可以确认是 next-key 锁，还是间隙锁，还是记录锁：

• 如果 LOCK_MODE 为 X，说明是 next-key 锁；
• 如果 LOCK_MODE 为 X， REC_NOT_GAP，说明是记录锁；
• 如果 LOCK_MODE 为 X， GAP，说明是间隙锁；

注意！插入意向锁名字里虽然有意向锁这三个字，但是它并不是意向锁，它属于行级锁，是一种特殊的间隙锁。

插入意向锁与间隙锁的另一个非常重要的差别是：尽管「插入意向锁」也属于间隙锁，但两个事务却不能在同一时间内，一个拥有间隙锁，另一个拥有该间隙区间内的插入意向锁（当然，插入意向锁如果不在间隙锁区间内则是可以的）。所以，插入意向锁和间隙锁之间是冲突的。

22.InnoDB 分析表锁定

为了研究行锁，暂时将自动 commit 关闭，set autocommit = 0;

show status like '%innodb_row_lock%';

• Innodb_row_lock_current_waits：当前正在等待锁的数量
• Innodb_row_lock_time：等待总时长。从系统启动到现在一共等待的时间
• Innodb_row_lock_time_avg：平均等待时长。从系统启动到现在一共等待的时间
• Innodb_row_lock_time_max：最大等待时长。从系统启动到现在一共等待的时间
• Innodb_row_lock_waits：等待次数。从系统启动到现在一共等待的时间

23.for update 的加锁过程

#执行普通的sql语句
select * from t_user where age < 20;

select * from performance_schema.data_locks;

可以看到，输出结果是空，说明普通 select 的查询语句， Innodb 存储引擎不会为事务加任何锁。

当我们对数据库表进行 DML 和 DDL 操作的时候，MySQL 会给这个表加上 MDL 锁，即元数据锁，MDL 锁是 server 层实现的表级锁，适用于所有存储引擎。

• 对一张表进行增删查改操作（DML 操作）的时候，加的是 MDL 读锁；
• 对一张表进行表结构变更操作（DDL 操作）的时候，加的是 MDL 写锁；

之所以需要 MDL 锁，就是因为事务执行的时候，不能发生表结构的改变，否则就会导致同一个事务中，出现混乱的现象，如果当前有事务持有 MDL 读锁，DDL 操作就不能申请 MDL 写锁，从而保证表元数据的数据一致性。

我们的事物 A 执行了普通 select 查询语句，如果要看该事务持有的 MDL 锁，可以通过这条命令

select * from performance_schema.metadata_locks;

因此，我们常说的普通查询不加锁，其实指的是不加 Innodb 的行级锁，但实际上是需要持有 MDL 锁的。

总结:在执行 select … for update 语句的时候，会有产生 2 个表级别的锁：

• 一个是 Server 层表级别的锁：MDL 锁。事务在进行增删查改的时候，server 层申请 MDL 锁都是 MDL 读锁，而 MDL 读锁之间是相互兼容的，MDL 读锁只会和 MDL 写锁发生冲突，在对表结构进行变更操作的时候，才会申请 MDL 写锁。
• 一个是 Inoodb 层表级别的锁：意向锁。事务在进行增删改和锁定读的时候，inoodb 层会申请意向锁，意向锁不会和行级锁发生冲突，而且意向锁之间也不会发生冲突，意向锁只会和共享表锁（lock tables ... read）和独占表锁（lock tables ... write）发生冲突。

如果 select … for update 语句的查询条件没有索引字段的话，整张表都无法进行增删改了，从这个现象看，好像是把表锁起来了，但是并不是因为上面这两个表级锁的原因。

而是因为如果锁定读查询语句，没有使用索引列作为查询条件，导致扫描是全表扫描。那么，每一条记录的索引上都会加 next-key 锁（行级锁），这样就相当于锁住的全表，这时如果其他事务对该表进行增、删、改操作的时候，都会被阻塞。

24.加锁规则

加锁规则，包含了两个“原则”、两个“优化”和一个“bug”。

• 原则 1:加锁的基本单位是 next-key lock。希望你还记得，next-key lock 是前开后闭区间。
• 原则 2：查找过程中访问到的对象才会加锁。
• 优化 1：索引上的等值查询，给唯一索引加锁的时候，next-key lock 退化为行锁。
• 优化 2：索引上的等值查询，向右遍历时且最后一个值不满足等值条件的时候，next-key lock 退化为间隙锁。
• 一个 bug：唯一索引上的范围查询会访问到不满足条件的第一个值为止。

对于原则 1 说的：加锁的基本单位是 Next-Key 锁，意思是默认都是先加上 Next-Key，之后根据 2 个优化点选择性退化为行锁或间隙锁。

对于原则 2 说的：访问到的对象才会加锁，意思是如果直接索引覆盖到了，不需要回表，那么就不会对聚簇索引加锁。这样的话，其他事务就可以对聚簇索引进行操作，而不会阻塞。